﻿using System;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Documents;
using System.Windows.Ink;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Animation;
using System.Windows.Shapes;
using System.Linq;
using System.Data.Linq;
using System.Text;
using System.Collections.ObjectModel;
namespace restaurant
{
    public class dbFunctionsRestaurant
    {
        private dbContextRestaurant dbcontext;
        private ObservableCollection<tblSeating> _tblseating;
        public ObservableCollection<tblSeating> tblseating
        {
            get
            {
                return _tblseating;
            }
            set
            {
                if (_tblseating != value)
                {
                    _tblseating = value;
                }
            }
        }
        private ObservableCollection<tblEmpJob> _tblempjob;
        public ObservableCollection<tblEmpJob> tblempjob
        {
            get
            {
                return _tblempjob;
            }
            set
            {
                if (_tblempjob != value)
                {
                    _tblempjob = value;
                }
            }
        }
        private ObservableCollection<tblEMPLOYEE> _tblemployee;
        public ObservableCollection<tblEMPLOYEE> tblemployee
        {
            get
            {
                return _tblemployee;
            }
            set
            {
                if (_tblemployee != value)
                {
                    _tblemployee = value;
                }
            }
        }
        private ObservableCollection<tblCustbill> _tblcustbill;
        public ObservableCollection<tblCustbill> tblcustbill
        {
            get
            {
                return _tblcustbill;
            }
            set
            {
                if (_tblcustbill != value)
                {
                    _tblcustbill = value;
                }
            }
        }
        private ObservableCollection<tblItemsOrdered> _tblitemsordered;
        public ObservableCollection<tblItemsOrdered> tblitemsordered
        {
            get
            {
                return _tblitemsordered;
            }
            set
            {
                if (_tblitemsordered != value)
                {
                    _tblitemsordered = value;
                }
            }
        }
        private ObservableCollection<tblMENUITEMS> _tblmenuitems;
        public ObservableCollection<tblMENUITEMS> tblmenuitems
        {
            get
            {
                return _tblmenuitems;
            }
            set
            {
                if (_tblmenuitems != value)
                {
                    _tblmenuitems = value;
                }
            }
        }
        public dbFunctionsRestaurant()
        {
            dbcontext = new dbContextRestaurant(dbContextRestaurant.DBConnectionString);
        }

        public string insert_into_tblseating(int TableID, string TableLocation, int TableSize)
        {
            try
            {
                tblseating = new ObservableCollection<tblSeating>();
                tblSeating seats = new tblSeating { id = TableID, location = TableLocation, size = TableSize };
                tblseating.Add(seats);
                dbcontext.tblSeatings.InsertOnSubmit(seats);
                dbcontext.SubmitChanges();
                return "Success";
            }
            catch (NullReferenceException excep)
            {
                return excep.Message;
            }
        }
        public string insert_in_empjob(int jobID, string jobdesc, float max, float min)
        {
            tblempjob = new ObservableCollection<tblEmpJob>();
            tblEmpJob job = new tblEmpJob { describe = jobdesc, id = jobID, max = max, min = min };
            tblempjob.Add(job);
            dbcontext.tblEmpjobs.InsertOnSubmit(job);
            dbcontext.SubmitChanges();
            return "Success";
        }
        public string insert_in_employee(int empid, string name, DateTime hiredate, float hourpay, int jobid)
        {
            tblemployee = new ObservableCollection<tblEMPLOYEE>();
            tblEMPLOYEE emp = new tblEMPLOYEE { empid = empid, jobid = jobid, hiredate = hiredate, hourpay = hourpay, name = name };
            tblemployee.Add(emp);
            dbcontext.tblEmployees.InsertOnSubmit(emp);
            dbcontext.SubmitChanges();
            return "success";
        }
        public ObservableCollection<tblSeating> get_from_tableseating()
        {
            var seating = from tblSeating seat in dbcontext.tblSeatings
                          select seat;
            tblseating = new ObservableCollection<tblSeating>(seating);
            return tblseating;
        }
        public ObservableCollection<tblEMPLOYEE> get_from_emp()
        {
            var employee = from tblEMPLOYEE emp in dbcontext.tblEmployees
                           select emp;
            tblemployee = new ObservableCollection<tblEMPLOYEE>(employee);
            return tblemployee;
        }
        public void insert_into_menuitems(string desc, int menunumber, float price, string type)
        {
            tblmenuitems = new ObservableCollection<tblMENUITEMS>();
            tblMENUITEMS item = new tblMENUITEMS { desc = desc, menunumber = menunumber, menuprice = price, menutype = type };
            tblmenuitems.Add(item);
            dbcontext.tblMenuitems.InsertOnSubmit(item);
            dbcontext.SubmitChanges();
        }
        public void insert_into_cutomerbill(int tblid, int empid, DateTime start, DateTime stop, int billnum = 1)
        {
            tblcustbill = new ObservableCollection<tblCustbill>();
            tblCustbill bill = new tblCustbill { Bill = billnum, empid = empid, tableid = tblid, StartTime = start, EndTime = stop };
            tblcustbill.Add(bill);
            dbcontext.tblCustbills.InsertOnSubmit(bill);
            dbcontext.SubmitChanges();
        }
        public void insert_into_itemsordered(int billnum, int qty, int minum)
        {
            tblitemsordered = new ObservableCollection<tblItemsOrdered>();
            tblItemsOrdered order = new tblItemsOrdered { billnum = billnum, menuid = minum, Qty = qty };
            tblitemsordered.Add(order);
            dbcontext.tblItemsordered.InsertOnSubmit(order);
            dbcontext.SubmitChanges();
        }
        public string question1()
        {
            var query1 = from tblEmpJob emp in dbcontext.tblEmpjobs
                         where emp.max < 15.00
                         select new { empid = emp.id, paydiff = emp.max - emp.min };
            query1.ToList();
            StringBuilder answer = new StringBuilder();
            foreach (var item in query1)
            {
                answer.Append(Environment.NewLine);
                answer.AppendFormat("EMP ID : {0} , DIFF : {1}", item.empid, item.paydiff.ToString());
            }
            return answer.ToString();
        }
        public string question2()
        {
            var query2 = from tblEMPLOYEE emp in dbcontext.tblEmployees
                         where emp.hourpay < emp.empJob.min
                         orderby emp.hourpay
                         select new { EmpName = emp.name, EmpHireDate = emp.hiredate, EmpHourPay = emp.hourpay, EJobMinPay = emp.empJob.min };
                         

            /*var query2 = from tblEMPLOYEE emp in dbcontext.tblEmployees
                         join tblEmpJob in dbcontext.tblEmpjobs on emp.jobid equals tblEmpJob.id          
                         where emp.hourpay < tblEmpJob.min
                         orderby emp.hourpay
                         select new { EmpName = emp.name, EmpHireDate = emp.hiredate, EmpHourPay = emp.hourpay, EJobMinPay = tblEmpJob.min };*/
            query2.ToList();
            StringBuilder answer = new StringBuilder();
            foreach (var item in query2)
            {
                answer.Append(Environment.NewLine);
                answer.AppendFormat("Name : {0}, HireDate : {1}, HourPay : {2}, MinPay : {3}", item.EmpName, item.EmpHireDate, item.EmpHourPay, item.EJobMinPay);
            }             
            return answer.ToString();
        }
        public string question3()
        {
            var query3 = from tblCustbill bill in dbcontext.tblCustbills
                         join tblSeating in dbcontext.tblSeatings on bill.tableid equals tblSeating.id
                         join tblEMPLOYEE in dbcontext.tblEmployees on bill.empid equals tblEMPLOYEE.empid
                         select new { tblid = bill.tableid, name = bill.empid };
            query3.ToList();
            StringBuilder answer = new StringBuilder();
            foreach (var item in query3)
            {
                answer.Append(Environment.NewLine);
                answer.AppendFormat("Emplyee : {0}, Table : {1}", item.name, item.tblid);
            }
            return answer.ToString();
        }
        public string question4()
        {
            var query4 = from tblMENUITEMS menuitem in dbcontext.tblMenuitems
                         where !(from x in dbcontext.tblItemsordered
                                 select x.menuid).Contains(menuitem.menunumber)
                         select new { minum = menuitem.menunumber, mitype = menuitem.menutype, miprice = menuitem.menuprice };
            query4.ToList();
            StringBuilder answer = new StringBuilder();
            foreach (var item in query4)
            {
                answer.Append(Environment.NewLine);
                answer.AppendFormat("Menu ID: {0} Type: {1} Price: {2}", item.minum, item.mitype, item.miprice);
            }
            return answer.ToString();
        }
        public string question5()
        {
            var query5 = from tblMENUITEMS dish in dbcontext.tblMenuitems
                         select dish.menuprice;
            return query5.Average().ToString();
            
        }
        public string question6()
        {
            var query6 = from tblItemsOrdered items in dbcontext.tblItemsordered
                         group items by items.menuid into g
                         join tblMENUITEMS in dbcontext.tblMenuitems on g.Key equals tblMENUITEMS.menunumber
                         let totalqty = g.Sum(items => items.Qty)
                         orderby totalqty
                         select new { qty = totalqty, desc = tblMENUITEMS.desc };

            query6.ToList();
            StringBuilder answer = new StringBuilder();
            foreach (var item in query6)
            {
                answer.Append(Environment.NewLine);
                answer.AppendFormat(" Dish : {0} , QTY : {1}", item.desc, item.qty);
            }

            return answer.ToString();
            
        }
        public string question7()
        {
            /*SELECT tblCUSTBILL.BillNum, SUM(MIPrice*QtyOrdered), 0.15*SUM(MIPrice*QtyOrdered)
FROM tblCUSTBILL, tblITEMSORDERED, tblMENUITEMS
WHERE tblITEMSORDERED.BillNum = tblCUSTBILL.BillNum
AND tblITEMSORDERED.MINum = tblMENUITEMS.MINum
GROUP BY tblITEMSORDERED.BillNum;*/
            var query7 = from tblItemsOrdered itemsOrdered in dbcontext.tblItemsordered
                        //  group itemsOrdered by itemsOrdered.billnum into b
                          join tblCustbill in dbcontext.tblCustbills on itemsOrdered.billnum equals tblCustbill.Bill
                          join tblMENUITEMS in dbcontext.tblMenuitems on itemsOrdered.menuid equals tblMENUITEMS.menunumber
                          select new{billNum = itemsOrdered.billnum, billSum = itemsOrdered.Qty};
            query7.ToList();
            StringBuilder answer = new StringBuilder();
            foreach (var item in query7)
            {
                answer.Append(Environment.NewLine);
                answer.AppendFormat("BillNum = {0}, Sum = {1}, Tip = {2}", item.billNum, item.billSum, item.billSum);
            }
            return answer.ToString();
        }
    }
}
